package com.tianqu.tms.www.statistics.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.www.statistics.dto.BusinessTransactionDto;
import com.tianqu.tms.www.statistics.dto.ComboDto;
import com.tianqu.tms.www.statistics.entity.TmsSellerRegisterDayCountDto;
import com.tianqu.tms.www.statistics.entity.TmsSellerRegisterMonthCountDto;


public class TmsSellerRegisterDayCountDao extends TmsDao{

	public TmsSellerRegisterDayCountDao() throws TmsDaoException {
		super();
	}

	/**
	 * 商户总数
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsSellerRegisterDayCountDto getTmsSellerRegisterDayCount() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsSellerRegisterDayCountDto dto = null;
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" COUNT( DISTINCT memberId) as cnt  ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_SELLER_REGISTER_DAY_COUNT);
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				dto = new TmsSellerRegisterDayCountDto();
				dto.setCnt(rs.getString("cnt"));
				
			}
			return dto;
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 在库车辆/车位使用率/今日销售量/今日收车量
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsSellerRegisterDayCountDto getInStorehouseCount() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		TmsSellerRegisterDayCountDto dto = new TmsSellerRegisterDayCountDto();
		try {
			StringBuffer sql = new StringBuffer("  SELECT	");
			sql.append(" * ");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_SELLER_REGISTER_DAY_COUNT);
			sql.append(" WHERE COUNTDATE = DATE_FORMAT(NOW(),'%Y-%m-%d') ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				dto = new TmsSellerRegisterDayCountDto();
				dto.setOnlineNum(rs.getInt("onlineNum"));
				dto.setRatio(rs.getDouble("ratio"));
				dto.setOfftakeNum(rs.getInt("offtakeNum"));
				dto.setBuyNum(rs.getInt("buyNum"));
			}
			return dto;
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 库存平均车价商户TOP5
	 * @return
	 * @throws TmsDaoException
	 */
	public List<ComboDto> getAverageCarPricesForMerchantsTop5() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<ComboDto> tmsSellerRegisterDayCountList = new ArrayList<ComboDto>();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" m.shortName,");
			sql.append(" s.averagePrice");
			sql.append(" FROM");
			sql.append(" (SELECT memberId, IFNULL(AVG(averagePrice), 0) AS averagePrice");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_SELLER_REGISTER_DAY_COUNT);
			sql.append(" WHERE countDate BETWEEN date_format(date_sub(CURDATE(),interval 90 day),'%Y-%m-%d')");
			sql.append(" AND date_format(CURDATE(),'%Y-%m-%d')");
			sql.append(" GROUP BY memberId");
			sql.append(" ORDER BY averagePrice DESC");
			sql.append(" LIMIT 5) s");
			sql.append(" INNER JOIN tms_member m");
			sql.append(" ON s.memberId = m.memberId");
			sql.append(" ORDER BY s.averagePrice");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				ComboDto dto = new ComboDto();
				dto.setName(rs.getString("shortName"));
				dto.setValue(rs.getString("averagePrice"));
				tmsSellerRegisterDayCountList.add(dto);
			}

			return tmsSellerRegisterDayCountList;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 库存量商户TOP5
	 * @return
	 * @throws TmsDaoException
	 */
	public List<ComboDto> getInventoryTop5() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<ComboDto> tmsSellerRegisterDayCountList = new ArrayList<ComboDto>();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" m.shortName,");
			sql.append(" s.onlineNum");
			sql.append(" FROM");
			sql.append(" (SELECT memberId, onlineNum");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_SELLER_REGISTER_DAY_COUNT);
			sql.append(" WHERE countDate BETWEEN date_format(date_sub(CURDATE(),interval 90 day),'%Y-%m-%d')");
			sql.append(" AND date_format(CURDATE(),'%Y-%m-%d')");
			sql.append(" GROUP BY memberId");
			sql.append(" ORDER BY onlineNum DESC");
			sql.append(" LIMIT 5) s");
			sql.append(" INNER JOIN tms_member m");
			sql.append(" ON s.memberId = m.memberId");
			sql.append(" ORDER BY s.onlineNum");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				ComboDto dto = new ComboDto();
				dto.setName(rs.getString("shortName"));
				dto.setValue(rs.getString("onlineNum"));
				tmsSellerRegisterDayCountList.add(dto);
			}

			return tmsSellerRegisterDayCountList;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	/**
	 * 上一个月同期月销售量/上一个月同期月收车量
	 * @return
	 * @throws TmsDaoException
	 */
	public BusinessTransactionDto getMonthLastMonthSales() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		BusinessTransactionDto businessTransactionDto = new BusinessTransactionDto();
		try {
			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" IFNULL(SUM(offtakeNum), 0) AS offtakeNum,");
			sql.append(" IFNULL(SUM(buyNum), 0) AS buyNum");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_SELLER_REGISTER_DAY_COUNT);
			sql.append(" WHERE countY = DATE_FORMAT(NOW(),'%Y')");
			sql.append(" AND countM = date_format(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%c')");
			sql.append(" AND countDate <= concat(countY,LPAD(countM, 2, 0), DATE_FORMAT(NOW(),'%d'))");
			sql.append(" GROUP BY countY, countM");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				
				//上一个月同期月销售量
				businessTransactionDto.setOfftakeNumLastMonth(rs.getBigDecimal("offtakeNum"));
				
				//上一个月同期月收车量
				businessTransactionDto.setBuyNumLastMonth(rs.getBigDecimal("buyNum"));
			}
			return businessTransactionDto;
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 当月新增库存数量/当月新增库存总值
	 * @return
	 * @throws TmsDaoException
	 */
	public TmsSellerRegisterMonthCountDto getMonthSales() throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		
		TmsSellerRegisterMonthCountDto dto = new TmsSellerRegisterMonthCountDto();
		String strDateYYYY = DateUtil.getCurrentYear();
		String strDateMM = DateUtil.getCurrentMonth();

		try {

			StringBuffer sql = new StringBuffer("SELECT");
			sql.append(" IFNULL(SUM(offtakeNum), 0) AS offtakeNum,");
			sql.append(" IFNULL(SUM(buyNum), 0) AS buyNum");
			sql.append(" FROM ");
			sql.append(TmsTableNames.TMS_SELLER_REGISTER_DAY_COUNT);
			sql.append(" WHERE countY = ");
			sql.append(strDateYYYY);
			sql.append(" AND countM = ");
			sql.append(strDateMM);
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			if (rs.next()) {
				dto.setOfftakeNum(rs.getInt("offtakeNum"));
				dto.setBuyNum(rs.getInt("buyNum"));
			}

			return dto;

		} catch (Exception e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
}
